﻿using Bnbjoy.Domain.Abstract;
using Bnbjoy.Domain.Entities;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Bnbjoy.Domain.Common;

namespace Bnbjoy.Domain.Concrete
{
    public class UserRepository : IUserRepository
    {
        public async Task<bool> Insert(User user, Profile profile)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                using (var transaction = bnbjoyBackendDbEntities.Database.BeginTransaction())
                {
                    try
                    {
                        bool insertUser = false, insertProfile = false;
                        var existedUser = await bnbjoyBackendDbEntities.Users.AsNoTracking().Where(u => u.UserName == user.UserName).FirstOrDefaultAsync();
                        if (existedUser == null)
                        {
                            bnbjoyBackendDbEntities.Users.Add(user);
                            insertUser = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        var existedProfile = await bnbjoyBackendDbEntities.Profiles.AsNoTracking().Where(p => p.UserId == profile.UserId).FirstOrDefaultAsync();
                        if (existedProfile == null)
                        {
                            bnbjoyBackendDbEntities.Profiles.Add(profile);
                            insertProfile = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        if (insertUser && insertProfile)
                            transaction.Commit();

                        return insertUser && insertProfile;
                    }
                    catch (DbEntityValidationException e)
                    {
                        foreach (var eve in e.EntityValidationErrors)
                        {
                            Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                                eve.Entry.Entity.GetType().Name, eve.Entry.State);
                            foreach (var ve in eve.ValidationErrors)
                            {
                                Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                    ve.PropertyName, ve.ErrorMessage);
                            }
                        }
                        throw;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }


        public async Task<User> Find(string userName, string password)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var user = await bnbjoyBackendDbEntities.Users.AsNoTracking().Where(u => u.UserName == userName && u.Password == password).FirstOrDefaultAsync();
                if (user != null)
                {
                    bnbjoyBackendDbEntities.Users.Attach(user);
                    user.LastLogin = DateTime.Now;
                    await bnbjoyBackendDbEntities.SaveChangesAsync();
                    return user;
                }
                else
                {
                    return null;
                }
            }
        }

        public async Task<User> FindByMobileNumber(string mobileNumber)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var user = await bnbjoyBackendDbEntities.Users.AsNoTracking().Where(u => u.Mobile == mobileNumber).FirstOrDefaultAsync();
                if (user != null)
                {
                    return user;
                }
                else
                {
                    return null;
                }
            }
        }

        public async Task<User> Retreive(string userName)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var user = await bnbjoyBackendDbEntities.Users.AsNoTracking().Where(u => u.UserName == userName).FirstOrDefaultAsync();
                if (user != null)
                {
                    return user;
                }
                else
                {
                    return null;
                }
            }
        }

        public async Task<User> RetreiveByEmail(string email)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var user = await bnbjoyBackendDbEntities.Users.AsNoTracking().Where(u => u.Email == email).FirstOrDefaultAsync();
                if (user != null)
                {
                    return user;
                }
                else
                {
                    return null;
                }
            }
        }

        public async Task<IEnumerable<dynamic>> RetreiveBE_Bnbs(string userName)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var user = await bnbjoyBackendDbEntities.Users.Where(r => r.UserName == userName).FirstOrDefaultAsync();
                var bnbs = await bnbjoyBackendDbEntities.EmployeePermissionMaps.Where(r => r.UserId == user.UId).Join(bnbjoyBackendDbEntities.BnbInfoes,
                    epm => epm.BnbId,
                    bnb => bnb.BnbId,
                    (epm, bnb) => new
                    {
                        UserId = epm.UserId,
                        BnbId = bnb.BnbId,
                        BnbName = bnb.BnbName,
                        RoleName = "BE",
                    }).ToListAsync();

                return bnbs;
            }
        }

        public async Task<IEnumerable<dynamic>> RetreiveBA_Bnbs(string userName)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var bnbs = await bnbjoyBackendDbEntities.Users.Where(r => r.UserName == userName).Join(bnbjoyBackendDbEntities.BnbInfoes,
                    user => user.UId,
                    bnb => bnb.OwnerId,
                    (user, bnb) => new
                    {
                        UserId = user.UId,
                        BnbId = bnb.BnbId,
                        BnbName = bnb.BnbName,
                        RoleName = "BA",
                    }).ToListAsync();

                return bnbs;
            }
        }

        public async Task<dynamic> RetreiveUserAndPermission(string userId, string role, string bnbId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    if (role == "BA")
                    {
                        var result = await bnbjoyBackendDbEntities.Users.Where(u => u.UId == userId).Join(
                              bnbjoyBackendDbEntities.BnbInfoes,
                              user => user.UId,
                              bnb => bnb.OwnerId,
                              (user, bnb) => new
                              {
                                  UserId = user.UId,
                                  UserName = user.UserName,
                                  RoleName = "BA",
                                  ProfileId = user.ProfileId,
                                  Permission = "*",
                                  BnbId = bnb.BnbId
                              }).Where(r => r.BnbId == bnbId).FirstOrDefaultAsync();

                        return result;
                    }
                    else if (role == "BE")
                    {
                        var result = await bnbjoyBackendDbEntities.Users.Where(u => u.UId == userId).Join(
                               bnbjoyBackendDbEntities.EmployeePermissionMaps,
                               user => user.UId,
                               epm => epm.UserId,
                               (user, epm) => new
                               {
                                   UserId = user.UId,
                                   UserName = user.UserName,
                                   RoleName = "BA",
                                   ProfileId = user.ProfileId,
                                   Permission = epm.Permission,
                                   BnbId = epm.BnbId
                               }).Where(r => r.BnbId == bnbId).FirstOrDefaultAsync();

                        return result;
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<dynamic> DashUserInfo(string userId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                return await bnbjoyBackendDbEntities.Users.Where(u => u.UId == userId).Join(bnbjoyBackendDbEntities.BnbInfoes,
                    user => user.UId,
                    bnbInfo => bnbInfo.OwnerId,
                    (user, bnbInfo) => new
                    {
                        UserId = user.UId,
                        UserName = user.UserName,
                        BnbId = bnbInfo.BnbId,
                        BnbName = bnbInfo.BnbName,
                        OnlineSale = bnbInfo.OnlineSale
                    }).FirstOrDefaultAsync();
            }
        }

        public async Task<dynamic> UserAndProfileInfo(string userName)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var ownerInfo = await bnbjoyBackendDbEntities.Users.Where(u => u.UserName == userName).Join(bnbjoyBackendDbEntities.Profiles,
                    user => user.UId,
                    profile => profile.UserId,
                    (user, profile) => new
                    {
                        OwnerId = user.UId,
                        OwnerName = user.UserName,
                        OwnerEmail = user.Email,
                        OwnerMobile = user.Mobile,
                        OwnerRealName = profile.RealName,
                        OwnerIdCard = profile.IdCard,
                        OwnerCert = profile.Cert
                    }).FirstOrDefaultAsync();

                return ownerInfo;
            }
        }

        public async Task<IEnumerable<dynamic>> RetreiveEmployeesInfo(string ownerId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    string sql = @"select u.UId, u.OwnerId, u.UserName, u.Enabled, u.Email, u.Mobile, u.CreatedTime, p.ProfileId, p.RealName, p.IdCard, p.Cert, epm.BnbId, epm.Permission, b.BnbId, b.BnbName from User u " +
    "inner join Profile p on u.UId = p.UserId " +
    "left join EmployeePermissionMap epm on u.UId = epm.UserId " +
    "left join BnbInfo b on b.BnbId = epm.BnbId " +
    "where u.UId in (select UId from User where OwnerId = @param1) order by u.CreatedTime asc ";

                    dynamic results = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", ownerId));
                    string resultJson = JsonConvert.SerializeObject(results);
                    List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);
                    var aggregate = list.GroupBy(x => x.UId).Select(group => new
                    {
                        EmployeeInfo = group.Select(n => new
                        {
                            OwnerId = n.OwnerId,
                            EmployeeId = n.UId,
                            EmployeeName = n.UserName,
                            EmployeeEmail = n.Email,
                            EmployeeMobile = n.Mobile,
                            Enabled = n.Enabled,
                            EmployeeRealName = n.RealName,
                        }).First(),
                        Permissions = group.Select(n => new { BnbId = n.BnbId, BnbName = n.BnbName, EmployeeRights = n.Permission }).ToList()
                    }).ToList();

                    //转换模型
                    List<dynamic> bResult = new List<dynamic>();
                    if (aggregate != null && aggregate.Count() > 0)
                    {
                        foreach (var employee in aggregate)
                        {
                            dynamic temp = new ExpandoObject();
                            temp.OwnerId = employee.EmployeeInfo.OwnerId;
                            temp.EmployeeId = employee.EmployeeInfo.EmployeeId;
                            temp.EmployeeName = employee.EmployeeInfo.EmployeeName;
                            temp.EmployeeEmail = employee.EmployeeInfo.EmployeeEmail;
                            temp.EmployeeMobile = employee.EmployeeInfo.EmployeeMobile;
                            temp.EmployeeRealName = employee.EmployeeInfo.EmployeeRealName;
                            temp.Enabled = employee.EmployeeInfo.Enabled;
                            temp.EmployeePermission = employee.Permissions;
                            bResult.Add(temp);
                        }
                        return bResult;
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<bool> UpdatePermission(string userId, string bnbId, string permissionStr)
        {
            try
            {
                using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
                {
                    var permissionMap = await bnbjoyBackendDbEntities.EmployeePermissionMaps.AsNoTracking().Where(m => m.UserId == userId && m.BnbId == bnbId).FirstOrDefaultAsync();
                    bnbjoyBackendDbEntities.EmployeePermissionMaps.Attach(permissionMap);
                    permissionMap.Permission = permissionStr;

                    return await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public async Task<IEnumerable<dynamic>> BnbPermissionList(string userId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    //根据userId得到用户拥有的和未拥有的bnb权限
                    string sql_all = "select b.BnbId, b.BnbName, u.UId from BnbInfo b inner join User u on u.OwnerId = b.OwnerId " +
                                     "where u.UId = @param1";
                    dynamic allBnbPermissionResult = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql_all, new MySqlParameter("param1", userId));
                    string allJson = JsonConvert.SerializeObject(allBnbPermissionResult);
                    List<dynamic> list_all = JsonConvert.DeserializeObject<List<dynamic>>(allJson);

                    //根据userId得到用户拥有的bnb权限
                    string sql_existed = "select b.BnbId, b.BnbName, u.UId from BnbInfo b inner join User u on u.OwnerId = b.OwnerId " +
                                         "inner join EmployeePermissionMap e on b.BnbId = e.BnbId and u.UId = e.UserId " +
                                         "where u.UId = @param1";
                    dynamic existedBnbPermissionResult = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql_existed, new MySqlParameter("param1", userId));
                    string existedJson = JsonConvert.SerializeObject(existedBnbPermissionResult);
                    List<dynamic> list_existed = JsonConvert.DeserializeObject<List<dynamic>>(existedJson);

                    List<dynamic> result = new List<dynamic>();
                    //遍历得到用户的权限列表
                    foreach (var p in list_all)
                    {
                        var temp = list_existed.Where(x => x.BnbId == p.BnbId).FirstOrDefault();
                        dynamic pItem = new ExpandoObject();
                        pItem.UserId = p.UId;
                        pItem.BnbId = p.BnbId;
                        pItem.BnbName = p.BnbName;
                        if (temp == null)
                        {
                            pItem.Auth = false;
                        }
                        else
                        {
                            pItem.Auth = true;
                        }
                        result.Add(pItem);
                    }

                    return result;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<EmployeePermissionMap> CheckBnbPermission(string userId, string bnbId) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
              var result = await bnbjoyBackendDbEntities.EmployeePermissionMaps.Where(e => e.UserId == userId && e.BnbId == bnbId).FirstOrDefaultAsync();
              return result;
            }
        }

        public async Task<IEnumerable<dynamic>> InsertDeleteBnbPermission(string userId, List<Tuple<string, bool>> list) 
        {
            List<EmployeePermissionMap> toAddList = new List<EmployeePermissionMap>();
            List<EmployeePermissionMap> toRemoveList = new List<EmployeePermissionMap>();

            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                using (var transaction = bnbjoyBackendDbEntities.Database.BeginTransaction())
                {
                    try
                    {
                        foreach (var tuple in list)
                        {
                            //如果在权限列表中存在相同userId和bnbId的记录，说明存在
                            EmployeePermissionMap epm = await CheckBnbPermission(userId, tuple.Item1);
                            if (epm != null && !tuple.Item2)
                            {
                                //如果找到,但是前端传入勾选项是false，则添加到移除列表
                                toRemoveList.Add(epm);
                                continue;
                            }

                            if (epm == null && tuple.Item2)
                            {
                                toAddList.Add(new EmployeePermissionMap
                                {
                                    UserId = userId,
                                    BnbId = tuple.Item1,
                                    Permission = "1,2,3,4,5,6,7", //默认新增民宿权限的权限列表
                                });
                            }
                        }

                        //批量添加/删除用户民宿权限
                        if (toAddList.Count() > 0) 
                        {
                            bnbjoyBackendDbEntities.EmployeePermissionMaps.AddRange(toAddList);
                            await bnbjoyBackendDbEntities.SaveChangesAsync();
                        }

                        if (toRemoveList.Count() > 0) 
                        {
                            foreach (var r in toRemoveList)
                                bnbjoyBackendDbEntities.EmployeePermissionMaps.Attach(r);

                            bnbjoyBackendDbEntities.EmployeePermissionMaps.RemoveRange(toRemoveList);
                            await bnbjoyBackendDbEntities.SaveChangesAsync();
                        }
                        
                        transaction.Commit();

                        //返回该用户更新后的bnb permission，用于更新列表
                        IEnumerable<dynamic> result = new List<dynamic>();
                        result = await EmployeeBnbPermissionItems(userId);
                        return result;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return null;
                    }
                }
            }
        }


        public async Task<IEnumerable<dynamic>> EmployeeBnbPermissionItems(string employeeId) 
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                var list = await bnbjoyBackendDbEntities.EmployeePermissionMaps.Where(map => map.UserId == employeeId).Join(bnbjoyBackendDbEntities.BnbInfoes,
                        map => map.BnbId,
                        bnb => bnb.BnbId,
                        (map, bnb) => new 
                        {
                            BnbId = map.BnbId,
                            BnbName = bnb.BnbName,
                            EmployeeRights = map.Permission
                        }
                    ).ToListAsync();

                return list;
            }
        }


    }
}
